# 导入pandas
import pandas as pd

# 导入数据库对象
from SaaSDevelTool.data_mysql_data import db

"""
SaaS 中控代码控制函数

"""


# 为企业添加对应的功能包
def create_tenement_package_func(
        tenement_id,  # 租户编号
        package_id  # 功能包编号
):
    # 查询功能包的基础信息
    query_sum_package_table_sql = """
        SELECT 
            package_id,
            package_name,
            package_remark,
            model_id,
            package_images,
            front_package_id
        FROM 
            db_saas_center.sum_package_table 
        WHERE 
            package_id = {}
        ;""".format(package_id)
    query_sum_package_table_data = db.findOne(query_sum_package_table_sql)

    # 查询当前企业是否已开启当前模块
    query_map_tenement_model_table_sql = """
        SELECT 
            tenement_id,
            model_id
        FROM 
            db_saas_center.map_tenement_model_table 
        WHERE 
            tenement_id = {}
        AND 
            model_id = {}
        ;""".format(tenement_id, query_sum_package_table_data['model_id'])
    query_map_tenement_model_table_data = db.findOne(query_map_tenement_model_table_sql)

    # 判断当前企业是否已开启功能包
    if not query_map_tenement_model_table_data:
        return {"status": False, "msg": "添加失败，当前企业未开启功能包对应的大模块。"}

    # 判断当前功能包的前置功能包
    if int(query_sum_package_table_data['front_package_id']) != 0:
        # 查询功能包与租户映射表
        query_map_tenement_package_table_sql = """
            SELECT 
                tenement_id,
                package_id
            FROM 
                db_saas_center.map_tenement_package_table
            WHERE 
                tenement_id = {} 
            AND 
                package_id = {}
            ;""".format(tenement_id, query_sum_package_table_data['front_package_id'])
        # 获取信息
        query_map_tenement_package_table_data = db.findOne(query_map_tenement_package_table_sql)
        # 判断
        if not query_map_tenement_package_table_data:
            return {"status": False, "msg": "添加失败，未开启当前功能包的前置功能包。"}

    # 查询梯队映射表是否存在对应的数据
    query_sum_package_level_sql = """
        SELECT 
            id,
            echelon_name,
            echelon_package_id,
            echelon_level,
            md_id
        FROM 
            db_saas_center.sum_package_level 
        WHERE 
            echelon_package_id = {}
        ;""".format(package_id)
    query_sum_package_level_data = db.findOne(query_sum_package_level_sql)
    # 判断
    if not query_sum_package_level_data:
        return {"status": False, "msg": "添加失败，当前功能包未配置对应的梯队位置。"}

    # 判断是否有对应的配置项
    query_sum_package_settings_table_sql = """
        SELECT 
            settings_id,
            settings_name,
            settings_value,
            package_id
        FROM 
            db_saas_center.sum_package_settings_table 
        WHERE 
            package_id = {}
        ;""".format(package_id)
    query_sum_package_settings_table_data = db.findAll(query_sum_package_settings_table_sql)
    # 判断
    if query_sum_package_settings_table_data:
        # 执行原始的循环操作
        for i in query_sum_package_settings_table_data:
            # 拼接sql语句进行插入操作
            create_base_info_contact_settings_sql = """
                INSERT INTO 
                    db_tenement_{}.base_info_contact_settings(
                        name, 
                        value
                    )
                VALUES
                    ('{}', '{}')
                ;""".format(
                tenement_id,
                i['settings_name'],
                i['settings_value']
            )
            # 执行语句
            db.update(create_base_info_contact_settings_sql)

    # 判断是否有对应的数据库表添加
    query_sum_package_create_name_table_sql = """
        SELECT 
            create_table_id,
            create_table_name,
            package_id
        FROM 
            db_saas_center.sum_package_create_name_table
        WHERE 
            package_id = {}
        ;""".format(package_id)

    # 查询
    query_sum_package_create_name_table_data = db.findAll(query_sum_package_create_name_table_sql)

    # 判断
    if query_sum_package_create_name_table_data:
        print("query_sum_package_settings_table_data",query_sum_package_create_name_table_data)
        # 循环操作
        for i in query_sum_package_create_name_table_data:
            # 查询数据库是否已存在当前表
            query_table_sql = """SHOW TABLES IN db_tenement_{} LIKE '{}'""".format(tenement_id, i['create_table_name'])
            # 获取数据
            query_table_data = db.findOne(query_table_sql)
            # 判断数据是否存在
            if not query_table_data:
                # 拼接语句创建对应的数据库
                create_table_sql = """CALL db_tenement_0.create_database(%(gs_id)s, %(tabname_list)s)"""
                # 创建游标对象
                conn = db.get_connection()
                cursor = conn.cursor()
                # 定义参数
                data_params_data = {
                    "gs_id": tenement_id,
                    "tabname_list": i['create_table_name']
                }
                # 执行创建语句
                cursor.execute(create_table_sql, data_params_data)
                # 提交至数据库
                conn.commit()

    # 查询当前模块对应的首页页面
    query_sum_page_resource_table_home_sql = """
        SELECT 
            resource_id,
            resource_name,
            resource_remark,
            resource_urls,
            resource_home_page,
            resource_images,
            package_id
        FROM 
            db_saas_center.sum_page_resource_table
        WHERE 
            package_id = {} 
        AND 
            resource_home_page = 0 
        ;""".format(package_id)
    # 查询
    query_sum_page_resource_table_home_data = db.findAll(query_sum_page_resource_table_home_sql)
    # 判断
    if query_sum_page_resource_table_home_data:
        # 循环首页列表
        for i in query_sum_page_resource_table_home_data:
            # 判断是否存在
            query_base_info_contact_resource_all_home_page_sql = """
                SELECT 
                    id,
                    as_name,
                    resource_id,
                    r_id,
                    md_id
                FROM 
                    db_tenement_{}.base_info_contact_resource_all_home_page 
                WHERE 
                    resource_id = {}
                AND 
                    md_id = {}
            ;""".format(
                tenement_id,
                i['resource_id'],
                query_sum_package_table_data['model_id'])

            # 查询对应的语句
            query_base_info_contact_resource_all_home_page_data = db.findOne(
                query_base_info_contact_resource_all_home_page_sql
            )

            if not query_base_info_contact_resource_all_home_page_data:
                # 编写插入首页的语句
                create_resource_home_page_sql = """
                    INSERT INTO 
                        db_tenement_{}.base_info_contact_resource_all_home_page(
                            as_name, 
                            resource_id, 
                            r_id, 
                            md_id
                        )
                    VALUES
                        ('{}', {}, 0, {})
                    ;""".format(
                    tenement_id,
                    i['resource_name'],
                    i['resource_id'],
                    query_sum_package_table_data['model_id']
                )

                # 执行插入语句
                db.update(create_resource_home_page_sql)

                # 插入对应的页面与企业映射
                create_resource_tenement_sql = """
                    INSERT INTO 
                        db_saas_center.map_tenement_resource(
                            tenement_id, 
                            resource_id
                        )
                    VALUES
                        ({},{})
                    ;""".format(tenement_id, i['resource_id'])
                # 执行插入语句
                db.update(create_resource_tenement_sql)

    # 查询当前页面模块对应的普通页面
    query_sum_page_resource_table_sql = """
        SELECT 
            resource_id,
            resource_name,
            resource_remark,
            resource_urls,
            resource_home_page,
            resource_images,
            package_id
        FROM 
            db_saas_center.sum_page_resource_table 
        WHERE 
            resource_home_page = 1 
        AND 
            package_id = {}
        ;""".format(package_id)
    # 查询
    query_sum_page_resource_table_data = db.findAll(query_sum_page_resource_table_sql)
    # 判断
    if query_sum_page_resource_table_data:
        # 2级 获取梯队位置名称、页面名称
        if int(query_sum_package_level_data['echelon_level']) == 2:
            # 查询对应企业是否已存在梯队位置名称
            query_base_info_contact_resource_all_sql = """
                SELECT 
                    id 
                FROM 
                    db_tenement_{}.base_info_contact_resource_all 
                WHERE 
                    as_name = '{}' 
                AND 
                    md_id = {}
                ;""".format(
                tenement_id,
                query_sum_package_level_data['echelon_name'],
                query_sum_package_table_data['model_id']
            )
            # 查询
            query_base_info_contact_resource_all_data = db.findOne(query_base_info_contact_resource_all_sql)
            # 不存在直接进行添加
            if not query_base_info_contact_resource_all_data:
                # 拼接添加语句
                create_base_info_contact_resource_all_sql = """
                    INSERT INTO 
                        db_tenement_{}.base_info_contact_resource_all(
                            as_name, 
                            resource_id, 
                            r_id, 
                            md_id, 
                            `level`
                        )
                    VALUES
                        ('{}', 0, 0, {}, 1)
                    ;""".format(
                    tenement_id, query_sum_package_level_data['echelon_name'], query_sum_package_table_data['model_id'])
                # 执行查询语句
                r2_id = db.update(create_base_info_contact_resource_all_sql)

            # 获取id
            else:
                r2_id = query_base_info_contact_resource_all_data['id']

            # 循环对应的前端列表
            for res in query_sum_page_resource_table_data:
                # 查询是否已存在对应的页面
                query_base_info_contact_resource_all_sql_2 = """
                    SELECT 
                        resource_id,
                        md_id 
                    FROM 
                        db_tenement_{}.base_info_contact_resource_all 
                    WHERE 
                        resource_id = {}
                    AND 
                        md_id = {}
                ;""".format(tenement_id, res['resource_id'], query_sum_package_table_data['model_id'])
                # 判断
                query_base_info_contact_resource_all_data_2 = db.findOne(query_base_info_contact_resource_all_sql_2)
                # 不存在进行添加
                if not query_base_info_contact_resource_all_data_2:
                    # 执行添加操作
                    create_base_info_contact_resource_all_sql_2 = """
                        INSERT INTO 
                            db_tenement_{}.base_info_contact_resource_all(
                                as_name, 
                                resource_id, 
                                r_id, 
                                md_id, 
                                `level`
                        )
                        VALUES
                            ('{}',{},{},{},2)
                        ;""".format(
                        tenement_id, res['resource_name'], res['resource_id'],
                        r2_id, query_sum_package_table_data['model_id'])

                    db.update(create_base_info_contact_resource_all_sql_2)

                    # 添加企业与页面的映射
                    create_map_tenement_resource_sql = """
                        INSERT INTO 
                            db_saas_center.map_tenement_resource(
                                tenement_id, 
                                resource_id
                            )
                        VALUES
                            ({}, {})
                        ;""".format(tenement_id, res['resource_id'])
                    db.update(create_map_tenement_resource_sql)

        # 3级 获取梯队位置名称，功能包名称，页面名称
        elif int(query_sum_package_level_data['echelon_level']) == 3:
            # 查询对应企业是否已存在梯队位置名称
            query_base_info_contact_resource_all_sql = """
                SELECT 
                    id 
                FROM 
                    db_tenement_{}.base_info_contact_resource_all 
                WHERE 
                    as_name = '{}' 
                AND 
                    md_id = {}
                ;""".format(
                tenement_id,
                query_sum_package_level_data['echelon_name'],
                query_sum_package_table_data['model_id']
            )
            # 查询
            query_base_info_contact_resource_all_data = db.findOne(query_base_info_contact_resource_all_sql)
            # 不存在直接进行添加
            if not query_base_info_contact_resource_all_data:
                # 拼接添加语句
                create_base_info_contact_resource_all_sql = """
                    INSERT INTO 
                        db_tenement_{}.base_info_contact_resource_all(
                            as_name, 
                            resource_id, 
                            r_id, 
                            md_id, 
                            `level`
                        )
                    VALUES
                        ('{}', 0, 0, {}, 1)
                    ;""".format(
                    tenement_id, query_sum_package_level_data['echelon_name'], query_sum_package_table_data['model_id'])
                # 执行查询语句
                r1_id = db.update(create_base_info_contact_resource_all_sql)
            else:
                r1_id = query_base_info_contact_resource_all_data['id']

            # 查询2级功能包名称是否存在
            query_base_info_contact_resource_all_2_sql = """
                SELECT 
                    id, 
                    as_name, 
                    resource_id, 
                    r_id, 
                    md_id, 
                    `level`
                FROM 
                    db_tenement_{}.base_info_contact_resource_all 
                WHERE 
                    as_name = '{}'
                AND 
                    md_id = {} 
                AND 
                    level = 2 
            ;""".format(
                tenement_id, query_sum_package_table_data['package_name'], query_sum_package_table_data['model_id']
            )
            query_base_info_contact_resource_all_2_data = db.findOne(query_base_info_contact_resource_all_2_sql)
            # 如果存在
            if query_base_info_contact_resource_all_2_data:
                r2_id = query_base_info_contact_resource_all_2_data['id']
            # 如果不存在
            else:
                # 执行添加操作
                create_base_info_contact_resource_all_2_sql = """
                    INSERT INTO 
                        db_tenement_{}.base_info_contact_resource_all
                        (as_name, resource_id, r_id, md_id, `level`)
                    VALUES
                        ('{}',0, {}, {}, 2)
                    ;""".format(
                    tenement_id, query_sum_package_table_data['package_name'],
                    r1_id, query_sum_package_table_data['model_id'])

                # 执行添加操作
                r2_id = db.update(create_base_info_contact_resource_all_2_sql)
            # 添加三级
            for res in query_sum_page_resource_table_data:
                # 查询当前企业是否已存在对应的页面
                query_base_info_contact_resource_all_sql_3 = """
                    SELECT 
                        id, 
                        as_name, 
                        resource_id, 
                        r_id, 
                        md_id, 
                        `level`
                    FROM    
                        db_tenement_{}.base_info_contact_resource_all 
                    WHERE 
                        as_name = '{}' 
                    AND 
                        resource_id = {}
                    AND 
                        md_id = {}
                    ;""".format(tenement_id, res['resource_name'], res['resource_id'],
                                query_sum_package_table_data['model_id'])
                # 获取结果
                query_base_info_contact_resource_all_data_3 = db.findOne(query_base_info_contact_resource_all_sql_3)
                # 判断结果
                if not query_base_info_contact_resource_all_data_3:
                    # 插入企业自有数据库中数据
                    create_base_info_contact_resource_all_sql = """
                        INSERT INTO 
                            db_tenement_{}.base_info_contact_resource_all(
                                as_name, 
                                resource_id, 
                                r_id, 
                                md_id, 
                                `level`
                            )
                        VALUES
                            ('{}',{},{}, {},3)
                        ;""".format(
                        tenement_id,
                        res['resource_name'],
                        res['resource_id'], r2_id,
                        query_sum_package_table_data['model_id'])
                    # 执行插入操作
                    db.update(create_base_info_contact_resource_all_sql)

                    # 添加企业与页面的映射
                    create_map_tenement_resource_sql = """
                        INSERT INTO 
                            db_saas_center.map_tenement_resource(
                                tenement_id, 
                                resource_id
                            )
                        VALUES
                            ({}, {})
                        ;""".format(tenement_id, res['resource_id'])
                    db.update(create_map_tenement_resource_sql)

            # 判断企业与功能包的映射

        # 检测对应的功能包当前企业是否添加
        query_map_tenement_package_table_sql = """
            SELECT 
                tenement_id, 
                package_id
            FROM 
                db_saas_center.map_tenement_package_table 
            WHERE 
                tenement_id = {} 
            AND 
                package_id = {}
            ;""".format(tenement_id, package_id)
        # 获取到数据
        query_map_tenement_package_table_data = db.findOne(query_map_tenement_package_table_sql)
        # 如果没有进行添加
        if not query_map_tenement_package_table_data:
            # 拼接语句
            create_map_tenement_package_table_sql = """
                INSERT INTO 
                    db_saas_center.map_tenement_package_table(
                        tenement_id, 
                        package_id
                    )
                VALUES
                    ({},{})
                ;""".format(tenement_id, package_id)
            # 执行语句
            db.update(create_map_tenement_package_table_sql)
    return {"status": True, "msg": "功能包配置成功。"}


# 查询当前功能包有多少租户开启
def query_package_tenement_list(
        package_id  # 功能包编号
):
    """
    返回响应值：[{'tenement_id': 10},{'tenement_id': 11}]
    """
    # 查询对应的功能包
    query_map_tenement_package_table_sql = """
        SELECT 
            tenement_id 
        FROM 
            db_saas_center.map_tenement_package_table 
        WHERE 
            package_id = {}
        ;""".format(package_id)
    # 获取对应的值
    query_map_tenement_package_table_data = db.findAll(query_map_tenement_package_table_sql)
    # 返回对应的数据
    return query_map_tenement_package_table_data


# 修改页面名称的数据同步
def update_resource_name(
        resource_id,  # 页面编号
        resource_name  # 页面名称
):
    # 根据页面获取对应的功能包
    query_sum_page_resource_table_sql = """
        SELECT 
            resource_name,
            package_id,
            resource_home_page 
        FROM 
            db_saas_center.sum_page_resource_table 
        WHERE 
            resource_id = {}
        ;""".format(resource_id)

    # 获取到对应的数据
    query_sum_page_resource_table_data = db.findOne(query_sum_page_resource_table_sql)

    # 获取功能包开启的企业
    tenement_list = query_package_tenement_list(query_sum_page_resource_table_data['package_id'])

    # 循环企业列表
    for i in tenement_list:
        # 如果是首页
        if int(query_sum_page_resource_table_data['resource_home_page']) == 0:
            # 修改当前企业下的页面名称
            update_base_info_contact_resource_all_home_page_sql = """
                UPDATE  
                    db_tenement_{}.base_info_contact_resource_all_home_page
                SET 
                    as_name = '{}'  
                WHERE 
                    resource_id = {}
            ;""".format(i['tenement_id'], resource_name, resource_id)
            # 执行语句
            db.update(update_base_info_contact_resource_all_home_page_sql)

        # 不是首页
        else:
            # 修改当前企业下的页面名称
            update_base_info_contact_resource_all_home_page_sql = """
                UPDATE  
                    db_tenement_{}.base_info_contact_resource_all 
                SET 
                    as_name = '{}'  
                WHERE 
                    resource_id = {}
            ;""".format(i['tenement_id'], resource_name, resource_id)
            # 执行语句
            db.update(update_base_info_contact_resource_all_home_page_sql)
